package com.wzh.codegen.biz.util;

import cn.hutool.core.map.MapUtil;
import com.wzh.codegen.biz.domain.dto.CodegenTableDTO;
import com.wzh.codegen.biz.domain.dto.DatasourceDTO;
import com.wzh.common.general.exception.CustomerException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;

/**
 * 数据库工具类
 *
 * @author wzh
 * @since 2024-01-01
 */
@Slf4j
@Component
public class DatabaseUtils {

    /**
     * 获取数据库连接
     */
    private Connection getConnection(DatasourceDTO datasource) throws SQLException, ClassNotFoundException {
        Class.forName(datasource.getDriverClassName());
        return DriverManager.getConnection(datasource.getUrl(), datasource.getUsername(), datasource.getPassword());
    }

    /**
     * 获取数据库表列表
     */
    public List<Map<String, Object>> getTables(DatasourceDTO datasource) {
        List<Map<String, Object>> tables = new ArrayList<>();

        try (Connection connection = getConnection(datasource)) {
            DatabaseMetaData metaData = connection.getMetaData();

            String catalog = connection.getCatalog();
            String schema = getSchema(datasource.getDbType(), connection);

            try (ResultSet rs = metaData.getTables(catalog, schema, "%", new String[]{"TABLE"})) {
                while (rs.next()) {
                    Map<String, Object> table = new HashMap<>();
                    table.put("tableName", rs.getString("TABLE_NAME"));
                    table.put("tableComment", rs.getString("REMARKS"));
                    table.put("tableType", rs.getString("TABLE_TYPE"));
                    tables.add(table);
                }
            }
        } catch (Exception e) {
            log.error("获取表列表失败", e);
            throw new CustomerException("获取表列表失败: " + e.getMessage());
        }

        return tables;
    }

    /**
     * 获取表字段信息
     */
    public List<Map<String, Object>> getTableColumns(DatasourceDTO datasource, String tableName) {
        List<Map<String, Object>> columns = new ArrayList<>();

        try (Connection connection = getConnection(datasource)) {
            DatabaseMetaData metaData = connection.getMetaData();

            String catalog = connection.getCatalog();
            String schema = getSchema(datasource.getDbType(), connection);

            // 获取主键信息
            Set<String> primaryKeys = new HashSet<>();
            try (ResultSet pkRs = metaData.getPrimaryKeys(catalog, schema, tableName)) {
                while (pkRs.next()) {
                    primaryKeys.add(pkRs.getString("COLUMN_NAME"));
                }
            }

            // 获取字段信息
            try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, "%")) {
                while (rs.next()) {
                    Map<String, Object> column = new HashMap<>();
                    String columnName = rs.getString("COLUMN_NAME");

                    column.put("columnName", columnName);
                    column.put("dataType", rs.getString("TYPE_NAME"));
                    column.put("columnSize", rs.getInt("COLUMN_SIZE"));
                    column.put("decimalDigits", rs.getInt("DECIMAL_DIGITS"));
                    column.put("nullable", rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                    column.put("columnComment", rs.getString("REMARKS"));
                    column.put("columnDefault", rs.getString("COLUMN_DEF"));
                    column.put("isPrimaryKey", primaryKeys.contains(columnName));
                    column.put("isAutoIncrement", "YES".equals(rs.getString("IS_AUTOINCREMENT")));

                    // 转换为Java类型
                    String javaType = convertToJavaType(rs.getString("TYPE_NAME"), rs.getInt("DATA_TYPE"));
                    column.put("javaFieldType", javaType);
                    column.put("javaFieldName", CodegenUtils.toCamelCase(columnName));

                    columns.add(column);
                }
            }
        } catch (Exception e) {
            log.error("获取表字段信息失败", e);
            throw new CustomerException("获取表字段信息失败: " + e.getMessage());
        }

        return columns;
    }

    /**
     * 同步表结构
     */
    public void syncTableStructure(DatasourceDTO datasource, String tableName) {
        try {
            // 获取最新的表结构
            List<Map<String, Object>> columns = getTableColumns(datasource, tableName);

            // TODO: 实现表结构同步逻辑
            log.info("同步表结构: {}, 字段数量: {}", tableName, columns.size());
        } catch (Exception e) {
            log.error("同步表结构失败", e);
            throw new CustomerException("同步表结构失败: " + e.getMessage());
        }
    }

    /**
     * 创建数据库表
     */
    public void createTable(DatasourceDTO datasource, String tableName, List<Map<String, Object>> columns, CodegenTableDTO dto) {
        try (Connection connection = getConnection(datasource)) {
            // 检查表是否已存在
            if (tableExists(connection, tableName)) {
                throw new CustomerException("表 " + tableName + " 已存在");
            }

            // 生成CREATE TABLE语句
            String createSql = generateCreateTableSql(tableName, columns, datasource.getDbType(), dto.getTableComment());

            // 执行创建表语句
            try (Statement statement = connection.createStatement()) {
                statement.execute(createSql);
                log.info("成功创建表: {}", tableName);
            }
        } catch (Exception e) {
            log.error("创建表失败", e);
            throw new CustomerException("创建表失败: " + e.getMessage());
        }
    }

    /**
     * 检查表是否存在
     */
    private boolean tableExists(Connection connection, String tableName) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        String catalog = connection.getCatalog();
        // 假设使用MySQL
        String schema = getSchema("mysql", connection);

        try (ResultSet rs = metaData.getTables(catalog, schema, tableName, new String[]{"TABLE"})) {
            return rs.next();
        }
    }

    /**
     * 生成CREATE TABLE语句
     */
    public String generateCreateTableSql(String tableName, List<Map<String, Object>> columns, String dbType, String tableComment) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ").append(tableName).append(" (\n");

        List<String> primaryKeys = new ArrayList<>();

        for (int i = 0; i < columns.size(); i++) {
            Map<String, Object> column = columns.get(i);
            String columnName = (String) column.get("columnName");
            String dataType = convertDataType(MapUtil.getStr(column, "dataType"), dbType);
            Boolean nullable = (Boolean) column.get("nullable");
            String comment = (String) column.get("columnComment");
            Boolean isPrimaryKey = (Boolean) column.get("isPrimaryKey");
            Boolean isAutoIncrement = (Boolean) column.get("isAutoIncrement");
            // 新增：默认值
            String columnDefault = (String) column.get("columnDefault");
            // 新增：ON UPDATE CURRENT_TIMESTAMP支持
            Boolean onUpdateCurrentTimestamp = (Boolean) column.get("onUpdateCurrentTimestamp");
            String extra = (String) column.get("extra");

            sql.append("    ").append(columnName).append(" ").append(dataType);

            if (Boolean.TRUE.equals(isAutoIncrement) && "mysql".equalsIgnoreCase(dbType)) {
                sql.append(" AUTO_INCREMENT");
            }

            if (Boolean.FALSE.equals(nullable)) {
                sql.append(" NOT NULL");
            }

            // 默认值处理
            if (columnDefault != null && !columnDefault.isEmpty()) {
                if (isUnquotedDefaultValue(columnDefault)) {
                    sql.append(" DEFAULT ").append(columnDefault);
                } else {
                    if ("mysql".equalsIgnoreCase(dbType)) {
                        sql.append(" DEFAULT '").append(columnDefault).append("'");
                    } else if ("sqlserver".equalsIgnoreCase(dbType)) {
                        sql.append(" DEFAULT ").append(columnDefault);
                    } else {
                        sql.append(" DEFAULT '").append(columnDefault).append("'");
                    }
                }
            }

            // 处理ON UPDATE CURRENT_TIMESTAMP
            if (Boolean.TRUE.equals(onUpdateCurrentTimestamp) && "mysql".equalsIgnoreCase(dbType)) {
                sql.append(" ON UPDATE CURRENT_TIMESTAMP");
            }

            // 处理额外属性
            if (extra != null && !extra.trim().isEmpty() && "mysql".equalsIgnoreCase(dbType)) {
                sql.append(" ").append(extra);
            }

            if (comment != null && !comment.trim().isEmpty() && "mysql".equalsIgnoreCase(dbType)) {
                sql.append(" COMMENT '").append(comment).append("'");
            }


            if (Boolean.TRUE.equals(isPrimaryKey)) {
                primaryKeys.add(columnName);
            }

            if (i < columns.size() - 1 || !primaryKeys.isEmpty()) {
                sql.append(",");
            }
            sql.append("\n");
        }

        // 添加主键约束
        if (!primaryKeys.isEmpty()) {
            sql.append("    PRIMARY KEY (").append(String.join(", ", primaryKeys)).append(")\n");
        }

        // 表注释
        sql.append(") comment").append(" '").append(tableComment).append("'");

        return sql.toString();
    }

    /**
     * 获取数据库Schema
     */
    private String getSchema(String dbType, Connection connection) throws SQLException {
        if ("postgresql".equalsIgnoreCase(dbType)) {
            return "public";
        } else if ("oracle".equalsIgnoreCase(dbType)) {
            return connection.getMetaData().getUserName().toUpperCase();
        }
        return null;
    }

    /**
     * 转换为Java类型
     */
    private String convertToJavaType(String sqlType, int jdbcType) {
        return switch (jdbcType) {
            case Types.BIGINT -> "Long";
            case Types.INTEGER, Types.SMALLINT, Types.TINYINT -> "Integer";
            case Types.DECIMAL, Types.NUMERIC -> "BigDecimal";
            case Types.FLOAT, Types.REAL -> "Float";
            case Types.DOUBLE -> "Double";
            case Types.BOOLEAN, Types.BIT -> "Boolean";
            case Types.DATE -> "LocalDate";
            case Types.TIME -> "LocalTime";
            case Types.TIMESTAMP -> "LocalDateTime";
            case Types.BLOB, Types.LONGVARBINARY, Types.VARBINARY -> "byte[]";
            default -> "String";
        };
    }

    /**
     * 转换数据类型
     */
    public String convertDataType(String sourceType, String targetDbType) {
        sourceType = sourceType.toUpperCase();
        targetDbType = targetDbType.toLowerCase();

        // MySQL类型映射
        if ("mysql".equals(targetDbType)) {
            return switch (sourceType) {
                case "BIGINT" -> "BIGINT";
                case "INTEGER", "INT" -> "INT";
                case "SMALLINT" -> "SMALLINT";
                case "TINYINT" -> "TINYINT";
                case "DECIMAL", "NUMERIC" -> "DECIMAL(10,2)";
                case "FLOAT" -> "FLOAT";
                case "DOUBLE" -> "DOUBLE";
                case "BOOLEAN", "BIT" -> "TINYINT(1)";
                case "DATE" -> "DATE";
                case "TIME" -> "TIME";
                case "TIMESTAMP", "DATETIME" -> "DATETIME";
                case "TEXT", "LONGTEXT" -> "TEXT";
                default -> "VARCHAR(255)";
            };
        }

        // SQL Server类型映射
        if ("sqlserver".equals(targetDbType)) {
            return switch (sourceType) {
                case "BIGINT" -> "BIGINT";
                case "INTEGER", "INT" -> "INT";
                case "SMALLINT" -> "SMALLINT";
                case "TINYINT" -> "TINYINT";
                case "DECIMAL", "NUMERIC" -> "DECIMAL(10,2)";
                case "FLOAT" -> "FLOAT";
                case "DOUBLE" -> "FLOAT";
                case "BOOLEAN", "BIT" -> "BIT";
                case "DATE" -> "DATE";
                case "TIME" -> "TIME";
                case "TIMESTAMP", "DATETIME" -> "DATETIME";
                case "TEXT", "LONGTEXT" -> "TEXT";
                default -> "NVARCHAR(255)";
            };
        }

        // 默认返回原类型
        return sourceType;
    }

    /**
     * 判断默认值是否是无需引号的关键字或表达式（如 CURRENT_TIMESTAMP, NOW(), NULL）
     */
    private boolean isUnquotedDefaultValue(String defaultValue) {
        String upper = defaultValue.trim().toUpperCase();
        return upper.equals("NULL") ||
                upper.equals("CURRENT_TIMESTAMP") ||
                upper.contains("NOW()") ||
                upper.contains("CURRENT_DATE") ||
                upper.contains("CURRENT_TIME") ||
                upper.contains("UUID()");
    }


}